package top.wuyang.study.java24.dao;

import top.wuyang.study.java24.entity.TbSoftware;
import top.wuyang.study.java24.entity.TbSoftwareType;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class TbSoftwareDAO {

    public static final String INSERT = "insert into tb_software(type_id, software_name, description, vendor)  value (?, ?, ?, ?)";
    public static final String QUERY = "select a.*, b.type_name, b.description 'desc' from tb_software a inner join tb_software_type b on a.type_id = b.id";
    public static final String QUERY_BY_ID = "select * from tb_software where id = ?";
    public static final String DELETE = "delete from tb_software where id = ?";
    public static final String DELETE_BY_TYPE_ID = "delete from tb_software where type_id = ?";
    public static final String UPDATE = "update tb_software set type_id=?,software_name=?,description=?,vendor=? where id=?";




    // 添加软件
    public static int insert(TbSoftware software) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(INSERT);
        ps.setInt(1, software.getType_id());
        ps.setString(2, software.getSoftware_name());
        ps.setString(3, software.getDescription());
        ps.setString(4, software.getVendor());
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }

    // 查询所有
    public static List<TbSoftware> query() throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY);
        List<TbSoftware> list = new ArrayList<>();
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            TbSoftware software = new TbSoftware();
            software.setId(rs.getInt("id"));
            software.setType_id(rs.getInt("type_id"));
            software.setSoftware_name(rs.getString("software_name"));
            software.setDescription(rs.getString("description"));
            software.setVendor(rs.getString("vendor"));
            software.setCreate_time(rs.getTimestamp("create_time"));
            // 查询类型
            TbSoftwareType softwareType = new TbSoftwareType();
            softwareType.setType_name(rs.getString("type_name"));
            softwareType.setDescription(rs.getString("desc"));
            software.setSoftwareType(softwareType);

            list.add(software);
        }
        conn.close();
        return list;
    }

    // 删除分类中的所有软件
    public static int deleteByTypeId(Integer typeId) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(DELETE_BY_TYPE_ID);
        ps.setInt(1, typeId);
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }

    // 删除软件
    public static int delete(Integer id) throws Exception{
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(DELETE);
        ps.setInt(1, id);
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }

    // 根据id查询
    public static TbSoftware queryById(Integer id) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY_BY_ID);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        TbSoftware software = null;
        while (rs.next()) {
            software = new TbSoftware();
            software.setId(rs.getInt("id"));
            software.setType_id(rs.getInt("type_id"));
            software.setSoftware_name(rs.getString("software_name"));
            software.setDescription(rs.getString("description"));
            software.setVendor(rs.getString("vendor"));
            software.setCreate_time(rs.getTimestamp("create_time"));
        }
        conn.close();
        return software;
    }

    public static int update(TbSoftware software) throws Exception{
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(UPDATE);
        ps.setInt(1, software.getType_id());
        ps.setString(2, software.getSoftware_name());
        ps.setString(3, software.getDescription());
        ps.setString(4, software.getVendor());
        ps.setInt(5, software.getId());
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }

}
